package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.GridAreaInfo;
import com.sl.au.entity.PartBaseInfo;
import com.sl.au.entity.Part;

public interface PartBaseInfoRepository extends JpaRepository<PartBaseInfo, String> {
	/**
	 * 快速查询
	 * 
	 * @param key
	 * @return
	 */
	@Query("select a from PartBaseInfo a where a.partName like %?1%")
	Page<PartBaseInfo> getByKey(String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.part is null and  a.partName like %?1%")
	Page<PartBaseInfo> getByNoPartAndKey(String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.part is null")
	Page<PartBaseInfo> getAllByNoPart(Pageable pageable);

	@Query("select a from PartBaseInfo a where a.part.id = ?1 and a.partName like %?2% ")
	Page<PartBaseInfo> getByPartAndKey(String partId, String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.part.id = ?1")
	Page<PartBaseInfo> getAllByPart(String partId, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.part is not null")
	List<PartBaseInfo> getAllPart();
	
	@Query("select a from PartBaseInfo a where a.part.id = ?1")
	List<PartBaseInfo> getAllByPartType(String partId);

	@Query("select a from PartBaseInfo a where a.part.id = ?1 and a.gridAreaInfo.id = ?2 and a.partName like %?3% ")
	Page<PartBaseInfo> getByPartAndGridAndKey(String partId, String gridId, String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.part.id = ?1  and a.gridAreaInfo.id = ?2 ")
	Page<PartBaseInfo> getAllByPartAndGrid(String partId, String gridId, Pageable pageable);

	/****************** 部件权属部门相关查询 *******************/

	@Query("select a from PartBaseInfo a where a.hierarchy is null and a.partName like %?1%")
	Page<PartBaseInfo> getByNoHierarchyAndKey(String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.hierarchy is null")
	Page<PartBaseInfo> getAllByNoHierarchy(Pageable pageable);

	@Query("select a from PartBaseInfo a where a.hierarchy.id = ?1 and a.partName like %?2% ")
	Page<PartBaseInfo> getByHierarchyAndKey(String hierarchyId, String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.hierarchy.id = ?1")
	Page<PartBaseInfo> getAllByHierarchy(String hierarchyId, Pageable pageable);

	/****************** 部件管理部门相关查询 *******************/

	@Query("select a from PartBaseInfo a where a.manageHierarchy is null and a.partName like %?1%")
	Page<PartBaseInfo> getByNoManageHierarchyAndKey(String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.manageHierarchy is null")
	Page<PartBaseInfo> getAllByNoManageHierarchy(Pageable pageable);

	@Query("select a from PartBaseInfo a where a.manageHierarchy.id = ?1 and a.partName like %?2% ")
	Page<PartBaseInfo> getByManageHierarchyAndKey(String hierarchyId, String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.manageHierarchy.id = ?1")
	Page<PartBaseInfo> getAllByManageHierarchy(String hierarchyId, Pageable pageable);

	/****************** 部件维护部门相关查询 *******************/

	@Query("select a from PartBaseInfo a where a.maintainHierarchy is null and a.partName like %?1%")
	Page<PartBaseInfo> getByNoMaintainHierarchyAndKey(String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.maintainHierarchy is null")
	Page<PartBaseInfo> getAllByNoMaintainHierarchy(Pageable pageable);

	@Query("select a from PartBaseInfo a where a.maintainHierarchy.id = ?1 and a.partName like %?2% ")
	Page<PartBaseInfo> getByMaintainHierarchyAndKey(String hierarchyId, String key, Pageable pageable);

	@Query("select a from PartBaseInfo a where a.maintainHierarchy.id = ?1")
	Page<PartBaseInfo> getAllByMaintainHierarchy(String hierarchyId, Pageable pageable);

	/********************************************************/

	@Query("select a from PartBaseInfo a where a.partName=?1")
	PartBaseInfo findByName(String name);

	@Modifying
	@Query("update PartBaseInfo a set a.part = null, a.partIdentify = null, a.partOrderNum = 0 where a.part.id in (?1)")
	int removePart(String[] ids);

	@Query("select IFNULL( max(a.orderNum) , 0 ) + 1 from PartBaseInfo a where a.hierarchy.id = ?1")
	int getPartBaseInfoOrder(String hierarchyId);

	@Query("select a from PartBaseInfo a where a.gridAreaInfo = ?1 and a.partName like %?2% ")
	Page<PartBaseInfo> findByGridAreaInfoAndKey(GridAreaInfo gridAreaInfo, String key, Pageable pageable);

	Page<PartBaseInfo> findByGridAreaInfo(GridAreaInfo gridAreaInfo, Pageable pageable);
	
	@Query("select a from PartBaseInfo a where a.gridAreaInfo.id = ?1")
	List<PartBaseInfo>findByGridAreaId(String gridId);

	@Query("select IFNULL( max(a.partOrderNum) , 0 ) + 1 from PartBaseInfo a where a.part.id = ?1")
	int getPartBaseInfoOrderByPart(String partId);

	/********* 部件清除 组织信息 **********/
	@Modifying
	@Query("update PartBaseInfo a set a.hierarchy = null , a.hierarchyIdentify = null , a.orderNum = 0  where a.hierarchy.id in (?1)")
	int deleteHierarchy(String[] ids);

	@Modifying
	@Query("update PartBaseInfo a set a.manageHierarchy = null where a.manageHierarchy.id in (?1)")
	int deleteManageHierarchy(String[] ids);

	@Modifying
	@Query("update PartBaseInfo a set a.maintainHierarchy = null where a.maintainHierarchy.id in (?1)")
	int deleteMaintainHierarchy(String[] ids);

	/********* 部件清除网格所属区域信息 **********/
	@Modifying
	@Query("update PartBaseInfo a set a.divisionIdentify = null where a.gridAreaInfo.id in ( select b.id from GridAreaInfo b where b.division.id in (?1) )")
	int deleteDivision(String[] ids);

	@Query("select a from PartBaseInfo a where a.part.partType.id=?2 and a.gridAreaInfo is not null and(a.partName like %?1% or a.partIdentify like %?1%)")
	Page<PartBaseInfo> findBindPartOnlyCon(Pageable pageable, String con, String typeId);

	@Query("select a from PartBaseInfo a where  a.part.partType.id=?1 and a.gridAreaInfo is not null")
	Page<PartBaseInfo> findBindPartNone(Pageable pageable, String typeId);

	@Query("select a from PartBaseInfo a where a.gridAreaInfo.id in (?1) and a.part.partType.id=?3 and(a.partName like %?2% or a.partIdentify like %?2%)")
	Page<PartBaseInfo> findBindPartByGridIdsAndCon(Pageable pageable, String[] gridIds, String con, String typeId);

	@Query("select a from PartBaseInfo a where a.gridAreaInfo.id in (?1) and a.part.partType.id=?2")
	Page<PartBaseInfo> findBindPartByGridIds(Pageable pageable, String[] gridIds, String typeId);

	@Query("select a from PartBaseInfo a where a.id in (?1) and a.part.partType.id=?3 and(a.partName like %?2% or a.partIdentify like %?2%)")
	Page<PartBaseInfo> findBindPartByPartIdsAndCon(Pageable pageable, String[] partIds, String con, String typeId);

	@Query("select a from PartBaseInfo a where a.id in (?1) and a.part.partType.id=?2")
	Page<PartBaseInfo> findBindPartByPartIds(Pageable pageable, String[] partIds, String typeId);

	@Query("select a from PartBaseInfo a where a.part.partType.id=?2 and a.gridAreaInfo is null and(a.partName like %?1% or a.partIdentify like %?1%)")
	Page<PartBaseInfo> findUnbindPartOnlyCon(Pageable pageable, String con, String typeId);

	@Query("select a from PartBaseInfo a where a.part.partType.id=?1 and a.gridAreaInfo is null")
	Page<PartBaseInfo> findUnbindPartNone(Pageable pageable, String typeId);

	@Query("select a from PartBaseInfo a where a.part.partType.id=?3 and (a.gridAreaInfo.id not in (?1) or a.gridAreaInfo is null) and (a.partName like %?2% or a.partIdentify like %?2%)")
	Page<PartBaseInfo> findUnbindPartByGridIdsAndCon(Pageable pageable, String[] gridIds, String con, String typeId);

	@Query("select a from PartBaseInfo a where a.part.partType.id=?2 and (a.gridAreaInfo.id not in (?1) or a.gridAreaInfo is null)")
	Page<PartBaseInfo> findUnbindPartByGridIds(Pageable pageable, String[] gridIds, String typeId);

	@Query("select a from PartBaseInfo a where a.part.partType.value='1' and a.gridAreaInfo.id=?1")
	List<PartBaseInfo> findBindPartInfo(String id);
	
	@Query("select a from PartBaseInfo a where a.part in (select p from Part p where p.partType.value = '1')")
	List<PartBaseInfo> findVideoPartInfo();
	
	@Query("select a from PartBaseInfo a where a.deviceIdentify = ?1 ")
	List<PartBaseInfo> findPartBaseInfoByDeviceIdentify(String deviceIdentify);
}
